SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[pje_porServiciosDeAscenso] @idInscripcion as integer,@puntaje as float output ,@resultado as float output  AS

set dateformat mdy
set nocount on

declare @ASJ1 as int,@ASJ2 as int,@ASJ3 as int,@ASJ4 as int,@ASJ9 as int, @puntajeSAscenso as float
declare @F1 as float,@F2 as float,@F3 as float,@F4 as float,@F9 as float
declare  @idAgente as int,@CargoGenerico as int,@AsignaturaGenerica as int, @idTipoConvocatoria as int ,@fechaLimite as datetime
declare @v1i as datetime,@v1f as datetime,@v2i as datetime,@v2f as datetime,@fini as datetime,@ffin as datetime,@fFinProyectada as datetime, @fecha_aux as datetime
declare @nivel_del_cargo as integer --nivel del cargo en que se inscribió
declare @fechaCierreDeInscripciones as datetime,@fecharef as datetime
declare @CargoRealizado as int ,@AsignaturaRealizada as int,@Fecha_de_Alta_Servicio as datetime,@Fecha_de_Baja_Servicio as datetime,@Jerarquia as integer
declare @FechaInicioVacaciones as datetime, @fechafinVacaciones as datetime
declare @cantdias as integer,@cantDiasAcumulados as float, @calculo as float,@Pini as integer,@pfin as integer,@i as integer, @ids as integer,@MaximaCantidadDeAños as int
declare @topeServiciosAscenso float,@TopeServiciosMiembroTribunal float
declare @grupoIncumbencia as int
declare @nivel_insc as int
declare @ciclo as int
declare @modalidad_insc as int 
declare @suma as float
declare @CargoRealizado2 as int
DECLARE @Error INT

BEGIN TRANSACTION
             SET @ERROR=0

select  @idAgente=i.Agente,@cargoGenerico = i.CargoGenerico,@AsignaturaGenerica=i.AsignaturaGenerica,@idtipoConvocatoria=Conv.TipoConvocatoria,@FechaLimite=Conv.FechaLimite,@fechaCierreDeInscripciones=Conv.fechaCierre,@FechaInicioVacaciones=FechaInicioVacaciones,@FechaFinVacaciones=FechaFinVacaciones,@nivel_insc = i.nivel,@modalidad_insc=i.modalidad,@ciclo=ciclo
	from 
	tb_inscripcionesConvocatorias i 
	join tb_Convocatorias conv on i.Convocatoria=conv.idConvocatoria 
	where i.idInscripcionConvocatoria=@idInscripcion

select @MaximaCantidadDeAños =75
--print 'maxima cantidad años'
--print @MaximaCantidadDeAños
--print 'Cierre de ÇInscripciones'
--print @fechaCierreDeInscripciones

IF NOT EXISTS(select top 1 * from tempdb.dbo.sysobjects where name like '%##calendario1%')
	 begin
		--print 'creo la tabla calendario'
		create table ##calendario1 (id int identity (0,1) ,ids smallint default 0)
		IF Not EXISTS (SELECT name FROM tempdb.dbo.sysindexes  WHERE name = 'calendario1_id_ind')   CREATE INDEX calendario1_id_ind  ON ##calendario1 (id)
		set @i=0
		while @i < (@MaximaCantidadDeAños + 3)  *365-1
		begin
			insert ##calendario1 values (0)
			set @i=@i+1
		end
	  end	
	 --else drop table ##calendario
	else update ##calendario1 set ids=0


  if @nivel_insc = 3 and @ciclo = 2 set  @nivel_insc=4
select @grupoIncumbencia=case
	when @nivel_insc = 2 then 570
	when @nivel_insc = 3 then 570
	when @nivel_insc = 4 then 571
end

if  @idTipoConvocatoria  in (2,3,4,9)  --  TipoConvocatoria = 2 Ascensos 3=traslados
begin

	--supongo que existe una tabla calendario con un campo calend integer, y que tiene 65*365 filas suponiendo que la persona puede tener 65 años de servicios
--	update ##calendario set ids=0 --inicializo la tabla con ceros
	-- calend(0) corresponde al dia de 1/1/(corriente año - 60) años

	set @FechaRef = cast('01/01/'+ str(datepart(yy,getdate() ) - @MaximaCantidadDeAños ) as datetime)
	--print 'Fecha de referencia'
	--print @fecharef
	-- para leer el nivel al cual pertenece el caro en que se inscribió
	-- Set @nivel_del_cargo = SELECT Nivel FROM cargos WHERE Codigo = CodigoAsignaturaoCargo

SELECT  top 1 @suma =case when max(j.jerarquia) = -2 and (sum(distinct DATEDIFF (dd,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 120 and 180 then 0.12
			  when max(j.jerarquia) = -1 and (sum(distinct DATEDIFF (dd,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 120 and 180 then 0.25
			  when max(j.jerarquia) = 0 and (sum(distinct DATEDIFF (dd,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 120 and 180 then 0.375
			  when max(j.jerarquia) = 1 and (sum(distinct DATEDIFF (dd,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 120 and 180 then 0.5
			  when max(j.jerarquia) = 9 and (sum(distinct DATEDIFF (dd,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 120 and 180 then 0.25 
			else 0 end,  @CargoRealizado2=J.Cargorealizado
FROM 
				tb_ServiciosHistoricos sh 
				join tb_JerarquiaAscensos J on J.CargoRealizado=Sh.CargoGenerico and  J.AsignaturaRealizada=Sh.AsignaturaGenerica
				join tb_organizaciones  o on sh.ubicacionoriginal=o.idorganizaciones
				join tb_localidades loc on o.Localidad = loc.IdLocalidad 
				join tb_gruposDeIncumbencias G on G.idGrupoIncumbencia=sh.GrupoIncumbencias
		--		join tb_reluniongsgu R on r.idgrupounion = G.idGrupoIncumbencia
				join tb_incumbencias h on G.idGrupoIncumbencia=h.grupoIncumbencia
			WHERE sh.IdAgente=@idAgente and J.CargoAspirado=@CargoGenerico and  J.AsignaturaAspirada=@AsignaturaGenerica and h.CargoGenerico=@Cargogenerico and h.AsignaturaGenerica=@AsignaturaGenerica
			 and (loc.Departamento in (1, case @resultado when -9 then 2 else 1 end))
			and (h.grupoIncumbencia=@grupoIncumbencia or h.grupoIncumbencia=99) and sh.FechaPresentacion <= @FechaLimite
group by J.Cargorealizado
order by case when max(j.jerarquia) = -2 and (sum(distinct DATEDIFF (month,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 4 and 6 then 0.125
			  when max(j.jerarquia) = -1 and (sum(distinct DATEDIFF (month,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 4 and 6 then 0.25
			  when max(j.jerarquia) = 0 and (sum(distinct DATEDIFF (month,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 4 and 6 then 0.375
			  when max(j.jerarquia) = 1 and (sum(distinct DATEDIFF (month,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 4 and 6 then 0.5
			  when max(j.jerarquia) = 9 and (sum(distinct DATEDIFF (month,sh.FechaAlta,isnull(sh.FechaBaja,@fechaCierreDeInscripciones)))) between 4 and 6 then 0.25 
			else 0 end desc

print 'suma .......'
print @suma
print 'cargo realizado 2'
print @CargoRealizado2

if @suma>0 
begin
	 set @puntaje = @suma
	print 'puntaje'
	print @puntaje
	print 'Cargo realizado2'
	print  @CargoRealizado2
end 
else set  @CargoRealizado2=5190
--print 'cargo realizado 2'
--print @CargoRealizado2

	DECLARE  c CURSOR FORWARD_ONLY FOR
	SELECT distinct CargoRealizado,AsignaturaRealizada,S.FechaAlta,isnull(S.FechaBaja,@fechaCierreDeInscripciones),J.Jerarquia FROM 
		tb_Servicioshistoricos S 
		join tb_JerarquiaAscensos J on J.CargoRealizado=S.CargoGenerico and  J.AsignaturaRealizada=S.AsignaturaGenerica
		join tb_gruposDeIncumbencias G on G.idGrupoIncumbencia=S.GrupoIncumbencias
		join tb_organizaciones  o on s.ubicacionoriginal=o.idorganizaciones
--		join tb_reluniongsgu R on r.idgrupounion = G.idGrupoIncumbencia
		join tb_localidades loc on o.Localidad = loc.IdLocalidad 
		join tb_incumbencias h on G.idGrupoIncumbencia=h.grupoIncumbencia
	WHERE S.IdAgente=@idAgente and J.CargoAspirado=@CargoGenerico and  J.AsignaturaAspirada=@AsignaturaGenerica and j.tipodeconvocatoria=@idTipoConvocatoria
		and h.CargoGenerico=@Cargogenerico and h.AsignaturaGenerica=@AsignaturaGenerica and S.FechaPresentacion <= @FechaLimite
              	and  j.cargorealizado <> @CargoRealizado2 and (loc.Departamento in (1, case @resultado when -9 then 2 else 1 end))
	ORDER BY S.FechaAlta

              
	SET @Error = @@ERROR 
	IF @Error <> 0 GOTO Salir

	--set @puntaje = 0
	OPEN c
	FETCH NEXT FROM c INTO @CargoRealizado,@AsignaturaRealizada,@Fecha_de_Alta_Servicio,@Fecha_de_Baja_Servicio,@Jerarquia

	WHILE @@fetch_status = 0
	BEGIN
	            set @fini = @Fecha_de_Alta_Servicio
    
		    -- Fijo las Fechas de Baja abiertas     
		     --print ' Entré al while del cursor'

		set @ffin = isNull(@Fecha_de_Baja_Servicio,@FechaCierreDeInscripciones)  --si la fecha de baja es null le asigno la fecha de cierre de inscripciones
		if @Fecha_de_Baja_Servicio < @Fecha_de_Alta_Servicio or @Fecha_de_Baja_Servicio>@FechaCierreDeInscripciones or @Fecha_de_Baja_Servicio = cast('01/01/2000' as datetime) or @Fecha_de_Baja_Servicio = cast('01/01/1900' as datetime)
			set @ffin= @FechaCierreDeInscripciones
		else 
			set @ffin= @Fecha_de_Baja_Servicio
		--Calculo los períodos vacacionales mas proximos
		set @v1i=@FechaInicioVacaciones
		while @v1i > @fini
		begin
		 set @v1i =dateadd(yy,-1,@v1i)--cast(str(datepart(mm,@FechaInicioVacaciones)) + '/'+str(datepart(dd,@FechaInicioVacaciones))+'/'+str(datepart(yyyy,@v1i)-1) as datetime)
		end
		set @v1f = @v1i + (@FechaFinVacaciones - @FechaInicioVacaciones)
		set @v2f = @v1f
		while @v2f<@ffin or @v2f=@v1f 
		begin
			set @v2f = dateadd(yy,1,@v2f)
		end
		
		set @v2i = @v2f - (@FechaFinVacaciones - @FechaInicioVacaciones)

		-- Calculo la proyeccion del servicio
		--Empieza y Termina en las mismas Vacaciones

		if (@fini < @v1f  AND @ffin < @v1f)  set @cantdias= datediff(dd,@fini,@ffin)

		--   Empieza en unas vacaciones y termina en otras
		if @fini < @v1f AND @ffin >= @v2i  and @ffin < @v2f
		begin          --atencion, si fini y ffin tienen el mismo año, topear con 365
		--print 'Empieza en unas vacaciones y termina en otras ' 	
            		         If datepart (yyyy,@fini) = datepart(yyyy,@ffin) 
             	             		    set @cantdias = 365
                   	    	Else
              	          		set @cantdias = datediff(dd,@fini,@v2f)   --datediff(xx,comienzo,fin) = fin - comienzo
		end

		-- Empieza en Vacaciones y finaliza en periodo lectivo del mismo año
		
		if @fini < @v1f AND @ffin >= @v1f AND @ffin <= dateadd(yy,1,@v1i) 
			begin
				--print 'Empieza en Vacaciones y finaliza en periodo lectivo del mismo año '
				set @calculo=round(datediff(dd,@v1f,@ffin) * 1.33,0)
				exec @cantdias = sp_minimo 365, @calculo
				set @cantdias=@cantdias+ datediff(dd,@fini,@v1f)
				--set  @cantdias = (@v1f - @fini) +  min( 365, (@ffin - @v1f) * 1.33)
			end

		--Empieza en Vacaciones y finaliza en periodo lectivo de otro año
		
		if @fini < @v1f AND  @ffin >= dateadd(yy,1,@v1f) 
		begin
			--print 'Empieza en Vacaciones y finaliza en periodo lectivo de otro año '
			set @fecha_aux=@v2f
			set @fecha_aux=dateadd(dd,-365,@fecha_aux) -- ojo le resto 365 dias
			set @calculo=round (datediff(dd,@fecha_aux,@ffin)* 1.33,0)
			exec @cantdias = sp_minimo 365,  @calculo
			set @cantdias=@cantdias+datediff(dd,@fini,@fecha_aux)
			--set  @cantdias = min(365, (@ffin - (@v2f - 365)) * 1.33) + ((@v2f - 365) - @fini)
		end

		--Empieza en período Lectivo y finaliza en vacaciones del mismo año

		if @fini >= @v1f AND  @ffin >= (@v1i + 365)  AND @ffin <= dateadd(yy,1,@v1f) 
		begin
			--print 'Empieza en período Lectivo y finaliza en vacaciones del mismo año'


			set @fecha_aux=@v1i
			set @fecha_aux=dateadd(dd,365,@fecha_aux) -- ojo le sumo 365 dias
--			set @calculo = round(datediff(dd,@fini,dateadd(dd,365,@fecha_aux)) * 1.33,0)
			set @calculo = round(datediff(dd,@fini,@fecha_aux) * 1.33,0)
			exec @cantdias = sp_minimo 365,@calculo
			set @calculo=datediff(dd,@fini,@ffin)
			exec @cantdias= sp_maximo @cantdias,@calculo

		end

		--Empieza en periodo lectivo y termina en otras vacaciones

		if @fini >= @v1f and @ffin >= @v2i and datediff(yy,@v1i,@v2i) >1
		begin
			--print 'Empieza en periodo lectivo y termina en otras vacaciones '
			set   @cantdias = datediff(dd,@fini,@v2f)
		end
		--    Empieza y termina en el mismo periodo lectivo

		if @fini >= @v1f and @ffin < dateadd(year,1,@v1i)
			begin
				--print 'Empieza y termina en el mismo periodo lectivo '
				set @calculo=round(datediff(dd,@fini,@ffin) * 1.33,0)
				exec @cantdias =  sp_minimo 365, @calculo
			end	

		 --Empieza en periodo lectivo y termina en otro
		if   @fini >= @v1f and @ffin < @v2i and datediff(yy,@v1i,@v2i) >1 
		begin
			--print 'Empieza en periodo lectivo y termina en otro '
			set @fecha_aux=@v2f
			set @fecha_aux=dateadd(dd,-365,@fecha_aux) -- ojo le resto 365 dias
			set @calculo=round(datediff( dd,@fecha_aux,@ffin ) * 1.33,0)
			exec @cantdias=  sp_minimo 365,@calculo
			set @cantdias=@cantdias+datediff(dd,@fini,@fecha_aux)
		
		end
		
		-- corro la fecha de baja hasta la de baja del servicio más los días que le corresponde por el proporcional de vacaciones
		-- recordar que cantdias tiene los días de servicio más el proporcional
		
		print 'fecha de alta: '+ cast(@Fecha_de_Alta_Servicio as varchar) + '   fecha de baja: '+ cast(@Fecha_de_baja_Servicio as varchar)
		print 'parámetros de periodos vacacionales'
		print @v1i
		print @v1f
		print @v2i
		print @v2f
		
		
	print 'cantDias' + cast(@CantDias as varchar)
	print' '
		--Construyo Identificador del Servicio

             		    set @ids = @jerarquia + 3

		  if @cargoRealizado in (521,520,5128) and @AsignaturaRealizada=0  set @ids=9	-- Cargo de Miembro de Tribunal ---ojoooooooooooooooo ver esto !

           		set @FfinProyectada=@fini
		set @FfinProyectada=dateadd(dd,@cantdias,@FfinProyectada)
		set @pini = datediff(dd,@FechaRef,@fini)
		set @pfin = datediff(dd,@FechaRef,@FfinProyectada )
		set @i=cast(@pini as int)
		print 'pini ' +cast(@pini as varchar)
		print 'pfin ' +cast(@pfin as varchar)
		print 'idservicio' + 	cast(@ids as varchar)

		update ##calendario1 set ids =@ids where (id between @pini and @pfin -1) and @ids>ids

		FETCH NEXT FROM c INTO @CargoRealizado,@AsignaturaRealizada,@Fecha_de_Alta_Servicio,@Fecha_de_Baja_Servicio,@Jerarquia

	END --del while del cursor


	CLOSE c
	DEALLOCATE c 

	--Calculo los años en cada jerarquía
	set @ASJ1 = 0       --ANTERIOR DEL ANTERIOR
	set @ASJ2 = 0       -- CARGO INMEDIATO ANTERIOR
	set @ASJ3 = 0      --  IGUAL JERARQUIA
	set @ASJ4 = 0       -- CARGO POSTERIOR
	set @ASJ9 = 0      -- MIEMBRO DE TRIBUNAL

	SET @i=0


	select @ASJ1= count(*) from ##calendario1 where ids = 1
	select @ASJ2= count(*) from ##calendario1 where ids = 2
	select @ASJ3= count(*) from ##calendario1 where ids = 3
	select @ASJ4= count(*) from ##calendario1 where ids = 4
	select @ASJ9= count(*) from ##calendario1 where ids = 9

print 'Días en cada j'
print 'j1: ant del ant ' + cast (@ASJ1 as varchar)
print 'j2: anterior   ' + cast (@ASJ2 as varchar)
print 'j3:  ' + cast (@ASJ3 as varchar)
print 'j4:  ' + cast (@ASJ4 as varchar)
print 'j9:  ' + cast (@ASJ9 as varchar)




select @F1= case  when @ASJ1 between 120 and 180 then 0.25 else 0 end 
select @F2= case  when @ASJ2 between 120 and 180 then 0.25 else 0 end 
select @F3= case  when @ASJ3 between 120 and 180 then 0.25 else 0 end 
select @F4= case  when @ASJ4 between 120 and 180 then 0.25 else 0 end
select @F9= case  when @ASJ9 between 120 and 180 then 0.25 else 0 end


print 'F1 ' + cast (@F1 as varchar)
print 'F2:  ' + cast (@F2 as varchar)
print 'F3:  ' + cast (@F3 as varchar)
print 'F9:  ' + cast (@F9 as varchar)


	If (@ASJ1 % 365) >= 183 
	        set @ASJ1 =  floor(@ASJ1/ 365) + 1
	        Else
	        set @ASJ1 = floor (@ASJ1/ 365) 
	If (@ASJ2 % 365) >= 183 
	        set @ASJ2 =  floor(@ASJ2/ 365) + 1
	        Else
	        set @ASJ2 = floor (@ASJ2/ 365)
	If (@ASJ3 % 365) >= 183 
	        set @ASJ3 =  floor(@ASJ3/ 365) + 1
	        Else
	        set @ASJ3 = floor (@ASJ3/ 365)
	If (@ASJ4 % 365) >= 183 
	        set @ASJ4 =  floor(@ASJ4/ 365) + 1
	        Else
	        set @ASJ4 = floor (@ASJ4/ 365)
	If (@ASJ9 % 365) >= 183 
	        set @ASJ9 =  floor(@ASJ9/ 365) + 1
	        Else
	        set @ASJ9 = floor (@ASJ9/ 365)

	if @ASJ9 > @TopeServiciosMiembroTribunal set @PuntajeSAscenso=@TopeServiciosMiembroTribunal else set @PuntajeSAscenso=@ASJ9

	if exists(select top 1 * from tb_calificacionesdeinscripcion where inscripcion = @idInscripcion and concepto = 528)-- Servicios miembro tribunal
			update tb_calificacionesdeinscripcion set puntaje=isnull(@PuntajeSAscenso,0) where inscripcion=@idInscripcion and concepto=528
		else 	insert tb_calificacionesdeinscripcion (Inscripcion,Concepto,Puntaje) values (@idInscripcion,528,isnull(@PuntajeSAscenso,0)) 

 if @idTipoConvocatoria = 3 --Para traslados todos los servicios directivos valen 2 puntos por año
	SET @PuntajeSAscenso = ( @ASJ4+@F4) * 2 + (@ASJ3+@F3)  * 2  + (@ASJ2+@F2) * 2 + (@ASJ1+@F1) *2
else
	SET @PuntajeSAscenso = ( @ASJ4+@F4) * 2 + (@ASJ3+@F3)  * 1.5  + (@ASJ2+@F2) * 1.0 + (@ASJ1+@F1) * 0.5

set @puntaje= @puntaje + @PuntajeSAscenso


-- end -- del else del if sum(....
end -- del if @idTipoConvocatoria = 1 

print 'años de servicios J1 ' + cast (@ASJ1 as varchar)
print 'años de servicios J2 ' + cast (@ASJ2 as varchar)
print 'años de servicios J3 ' + cast (@ASJ3 as varchar)
print 'años de servicios J4 ' + cast (@ASJ4 as varchar)
print 'años de servicios J9 ' + cast (@ASJ9 as varchar)
print 'Puntaje en cargos de Ascenso ' + cast (@puntaje as varchar)

Salir:
IF @Error <> 0 
	ROLLBACK TRANSACTION
ELSE 
	COMMIT TRANSACTION
GO
GRANT EXECUTE ON  [dbo].[pje_porServiciosDeAscenso] TO [Juntas]
GO
